package com.leruan.aasimp.dao.impl;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.leruan.aasimp.bean.NewCarSaleBean;
import com.leruan.aasimp.bean.SaleCarTypeBean;
import com.leruan.aasimp.bean.UsedCarSaleMainBean;
import com.leruan.aasimp.constant.UsedCarConstant;
import com.leruan.aasimp.dao.CarMainInfoDao;
import com.leruan.aasimp.entity.CarMainInfo;

public class CarMainInfoDaoImpl extends HibernateDaoSupport implements CarMainInfoDao{

	public List<NewCarSaleBean> getNewCarList(Map<String, String> map, int pageNo)
			throws Exception {

		Session session = this.getSession();
		StringBuffer sb = new StringBuffer("select t1.id,t1.vin,t1.identifcationNum,t1.carOwnerName,t1.plateNum,t1.color," +
				"t1.status,t1.opeTime,t2.vendorName,t3.brand,t3.department,t3.carType,t1.money from CarMainInfo t1, CarVendorInfo t2, " +
				"CarBasicInfo t3 where t1.carBasicId = t3.id and t1.vendorId = t2.id ");
		
		if(map.get("brand") != null)
			sb.append(" and t3.brand = '" + map.get("brand") + "'");
		if(map.get("department") != null)
			sb.append(" and t3.department = '" +map.get("deparment")+"'");
		if(map.get("carType") != null)
			sb.append(" and t3.carType = '" +map.get("carType")+"'");
		if(map.get("vin") != null)
			sb.append(" and t1.vin = '" +map.get("vin")+"'");
		if(map.get("carOwnerName") != null)
			sb.append(" and t1.carOwnerName = '" +map.get("carOwnerName")+"'");
		if(map.get("identifcationNum") != null)
			sb.append(" and t1.identifcationNum = '" +map.get("identifcationNum")+"'");
		if(map.get("plateNum") != null)
			sb.append(" and t1.plateNum = '" +map.get("plateNum")+"'");
		if(map.get("color") != null)
			sb.append(" and t1.color = '" +map.get("color")+"'");
		if(map.get("vendorName") != null)
			sb.append(" and t2.vendorName = '" +map.get("vendorName")+"' ");

		java.sql.Date startDate = null;
		if(map.get("startTime") != null){
			startDate  = java.sql.Date.valueOf(map.get("startTime"));
			sb.append(" and t1.opeTime >= :startDate");
		}
		java.sql.Date endDate = null;
		if(map.get("endTime") != null){
			endDate  = java.sql.Date.valueOf(map.get("endTime"));
			sb.append(" and t1.opeTime <= :endDate");
		}
		
		sb.append(" order by t1.opeTime desc");
		Query query = session.createQuery(sb.toString());
		
		if(map.get("startTime") != null)
			query.setDate("startDate", startDate);
		if(map.get("endTime") != null)
			query.setDate("endDate", endDate);
			
		int first = (pageNo - 1) * UsedCarConstant.PAGE_SIZE_3;
		query.setFirstResult(first);
		query.setMaxResults(UsedCarConstant.PAGE_SIZE_3);
		
		List list = query.list();
		
		List<NewCarSaleBean> newCarList = new ArrayList<NewCarSaleBean>();
		
		for(int i = 0;i < list.size(); i++){
			NewCarSaleBean nBean = new NewCarSaleBean(); 
			Object[] object = (Object[]) list.get(i);
			nBean.setId(Long.parseLong(object[0].toString()));
			nBean.setVin(object[1].toString());
			nBean.setIdentifcationNum(object[2].toString());
			nBean.setCarOwnerName(object[3].toString());
			nBean.setPlateNum(object[4].toString());
			nBean.setColor(object[5].toString());
			nBean.setStatus(Long.parseLong(object[6].toString()));
			nBean.setOpeTime((Date)object[7]);
			nBean.setVendorName(object[8].toString());
			nBean.setBrand(object[9].toString());
			nBean.setDepartment(object[10].toString());
			nBean.setCarType(object[11].toString());
			nBean.setMoney(object[12].toString());
			newCarList.add(nBean);
		}
		
		return newCarList;
	}

	public Long getNewCarTotal(Map<String, String> map) throws Exception {
		
		Session session = this.getSession();
		StringBuffer sb = new StringBuffer("select count(t2.id) from CarMainInfo t1, CarVendorInfo t2, " +
				"CarBasicInfo t3 where t1.carBasicId = t3.id and t1.vendorId = t2.id");
		
		if(map.get("brand") != null)
			sb.append(" and t3.brand = '" + map.get("brand") + "'");
		if(map.get("department") != null)
			sb.append(" and t3.department = '" +map.get("deparment")+"'");
		if(map.get("carType") != null)
			sb.append(" and t3.carType = '" +map.get("carType")+"'");
		if(map.get("vin") != null)
			sb.append(" and t1.vin = '" +map.get("vin")+"'");
		if(map.get("carOwnerName") != null)
			sb.append(" and t1.carOwnerName = '" +map.get("carOwnerName")+"'");
		if(map.get("identifcationNum") != null)
			sb.append(" and t1.identifcationNum = '" +map.get("identifcationNum")+"'");
		if(map.get("plateNum") != null)
			sb.append(" and t1.plateNum = '" +map.get("plateNum")+"'");
		if(map.get("color") != null)
			sb.append(" and t1.color = '" +map.get("color")+"'");
		if(map.get("vendorName") != null)
			sb.append(" and t2.vendorName = '" +map.get("vendorName")+"'");
		
		java.sql.Date startDate = null;
		if(map.get("startTime") != null){
			startDate  = java.sql.Date.valueOf(map.get("startTime"));
			sb.append(" and t1.opeTime >= :startDate");
		}
		java.sql.Date endDate = null;
		if(map.get("endTime") != null){
			endDate  = java.sql.Date.valueOf(map.get("endTime"));
			sb.append(" and t1.opeTime <= :endDate");
		}
		
		sb.append(" order by t1.opeTime desc");
		Query query = session.createQuery(sb.toString());
		
		if(map.get("startTime") != null)
			query.setDate("startDate", startDate);
		if(map.get("endTime") != null)
			query.setDate("endDate", endDate);
		
		return (Long)query.uniqueResult();
	}

	public List<SaleCarTypeBean> getCarTypePh(Map<String, String> map)
			throws Exception {
		Session session = this.getSession();
		java.sql.Date startDate  = java.sql.Date.valueOf(map.get("startTime"));
		java.sql.Date endDate  = java.sql.Date.valueOf(map.get("endTime"));
		
		StringBuffer sb = new StringBuffer("select t2.id, count(t2.id) from CarMainInfo t1, CarBasicInfo t2 where t1.carBasicId = t2.id and t1.opeTime>=? and t1.opeTime<=? ");
		
		if(map.get("brand") != null)
			sb.append(" and t2.brand ='" + map.get("brand") + "'");
		if(map.get("department") != null)
			sb.append(" and t2.department ='"+map.get("department") + "'");
		if(map.get("carType") != null)
			sb.append(" and t2.carType ='"+map.get("carType") +"'");
		
		sb.append(" group by t2.id order by count(t2.id) desc");
		Query query = session.createQuery(sb.toString()).setDate(0, startDate).setDate(1, endDate);
		
		List list = query.list();
		
		List<SaleCarTypeBean> slist = new ArrayList<SaleCarTypeBean>();
		Float total = 0f;
		for(int i = 0;i < list.size(); i++){
			SaleCarTypeBean sBean = new SaleCarTypeBean(); 
			Object[] object = (Object[]) list.get(i);
			sBean.setPlacing(Integer.parseInt(object[0].toString()));
			sBean.setNum(Integer.parseInt(object[1].toString()));
			sBean.setId((i+1)+"");
			total += sBean.getNum();
			slist.add(sBean);
		}
		if(slist.size() == 0)
			return slist;
		String hql = "select brand,department,carType from CarBasicInfo  where id = ?";
		
		for(SaleCarTypeBean sBean : slist){
			sBean.setScale(sBean.getNum()/total * 100);
			Object[] uniqueResult = (Object[]) session.createQuery(hql).setString(0, sBean.getPlacing()+"").uniqueResult();
			sBean.setName(uniqueResult[0].toString() + uniqueResult[1].toString() +" "+ uniqueResult[2].toString());
		}
		return slist;
	}

	@SuppressWarnings("unchecked")
	public List<CarMainInfo> getCarsInfoByIdd(String usercard) {
		Session session = this.getSession();
		StringBuffer sb = new StringBuffer("from CarMainInfo where identifcationNum=? and status=1");
		return (List<CarMainInfo>) session.createQuery(sb.toString()).setString(0, usercard).list();
	}

	public CarMainInfo getCarsInfoByVin(String vin) {
		Session session = this.getSession();
		StringBuffer sb = new StringBuffer("from CarMainInfo where vin=? and status=1");
		return (CarMainInfo) session.createQuery(sb.toString()).setString(0, vin).uniqueResult();
	}

	public void update(CarMainInfo carMainInfo) {
		Session session = this.getSession();
		session.update(carMainInfo);
	}

}
